package com.hs.web;

import com.hs.util.JDBCUtils;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;

@WebServlet("/teacherCount")
public class TeacherServletCount extends HttpServlet {
    /*
        mvc:
        m --model 持久层数据 实体（数据库表列名）-dao接口（连接数据获取数据）-service（业务逻辑）
        v -- view （视图页面 html vue js） 负责把数据显示到页面上
        c -- controller(web控制层)主要是生成url路径 就是为了响应前端页面 v
        从 V出发（url）--先到C（控制解析url路径） -- m 获取数据（数据库操作）-
        m-c-v
     */

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        int teachercount=0;
        int todayTeacher=0;
        int weekTeachers=0;
        int monthTeachers=0;
        try {
            //聚合函数：avg,sum,count,max, min  (需要聚拢查询的)
            String sql="select count(*) from teacher";
            Object obj[]={};
            ResultSet res = JDBCUtils.query(sql, obj);
            if(res.next()){
                teachercount=res.getInt(1);
            }

            //当天新增的老师信息（需要根据当天的时间）
            //-- DATE()只是提取当前日期部分，只考虑日期不考虑时间
            // CURRENT_DATE() 获取当天的时间返回的也是当天时间
          String todysql=  "select count(*) from teacher where DATE(hiredate) = CURRENT_DATE()";
          Object todyobj[]={};
            ResultSet todyres = JDBCUtils.query(todysql, todyobj);
            if (todyres.next()){
                todayTeacher = todyres.getInt(1);
            }
            /*
            计算本周的统计数据
            -- 本周数据 YEARWEEK 年周的计算组合 第二个参数表示从每周的第一天开始
               select count(*) from teacher where YEARWEEK(hiredate,1) = YEARWEEK(CURDATE(),1)
             */
            String weeksql="select count(*) from teacher where YEARWEEK(hiredate,1) = YEARWEEK(CURDATE(),1)";
            Object weekobj[]={};
            ResultSet weekres = JDBCUtils.query(weeksql, weekobj);
            if(weekres.next()){
                weekTeachers =weekres.getInt(1);
            }
            /**
             * 计算本月新增的数据
             * -- 比较入职日期的年份YEAR(hiredate)  与当前日期的年份是否是相同YEAR（CURDATE()）
             * -- 还要比较入职日期的月份 MONTH（hiredate）与当前日期比较MONTH（CURDATE()）
             *
             * select count(*) from teacher where YEAR(hiredate)=YEAR(CURDATE())
             *  AND MONTH(hiredate) =MONTH(CURDATE())
             */
            String monthsql="select count(*) from teacher where YEAR(hiredate)=YEAR(CURDATE()) AND MONTH(hiredate) =MONTH(CURDATE())";
            Object monthobj[]={};
            ResultSet monthres = JDBCUtils.query(monthsql, monthobj);
            if(monthres.next()){
                monthTeachers= monthres.getInt(1);
            }


            //把数据发送给前端
            req.setAttribute("teachercount",teachercount);
            req.setAttribute("todayTeacher",todayTeacher);
            req.setAttribute("weekTeachers",weekTeachers);
            req.setAttribute("monthTeachers",monthTeachers);
            //把数据转发给jsp页面
            req.getRequestDispatcher("/pages/main.jsp").forward(req,resp);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCUtils.close();

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req,resp);
    }
}
